select  concat(t.nprojnr,concat(concat('-',t.nkassanr),concat('-',t.ntransnr))) as TRANSACTION,
        n.szname as POINT_DE_VENTE,f.szname as CAISSE,
        TO_CHAR(b.dtAusgabeDat,'dd/mm/yyyy HH24:MI') as DATEVENTE,
        y.szname as PERSONNE,
        d.szname as DUREE,
        tt.ntarifblattnr as CODE_GRILLETARIFAIRE,
        tt.szname as NOM_GRILLETARIFAIRE,
        t.NKASSIERID as Hotesse,
        b.FEINZELTARIF as Prix,
        t.FGESAMTPREIS,
        b.neingegsortstk,
        o.szname  as POOL,
        b.nlfdnr,
        l.sztext as PROMPT
        from 
        TABKASSATRANSAKT t
        inner join
        TABKASSABLOCKSATZ b
        ON
        concat(t.nprojnr,concat(concat('-',t.nkassanr),concat('-',t.ntransnr)))= 
        concat(b.nprojnr,concat(concat('-',b.nkassanr),concat('-',b.ntransnr)))
        LEFT JOIN Tabkundenkartentypdef d ON b.NKUNDENKARTENTYPNR = d.NKUNDENKARTENTYPNR
        LEFT JOIN tabtarifblattdef tt on b.ntarifblattnr = tt.ntarifblattnr
        INNER JOIN Tabkassakonf           f             ON (t.NKASSANR = f.NKASSANR AND t.NPROJNR = f.NPROJNR)
        INNER JOIN Tabstandortedef        n             ON (f.NSTANDORTNR = n.nstandortnr)
        LEFT JOIN tabperstypdef           y             ON b.NPERSTYPNR = y.NPERSTYPNR
       LEFT JOIN Tabpooldef o ON (b.NPOOLNR = o.NPOOLNR AND b.NPROJNR = o.NPROJNR)
        LEFT JOIN TABSTOREPROMPTMASTER s ON b.NTRANSNR = s.NTRANSNR AND b.NKASSANR = s.NKASSANR AND b.NPROJNR = s.NPROJNR
        LEFT JOIN TABSTOREPROMPTDETAIL l ON s.NPROMPTNR = l.NPROMPTNR AND s.NPROMPTZAEHLERNR = l.NPROMPTZAEHLERNR AND s.NKASSANR = l.NKASSANR AND s.NPROJNR = l.NPROJNR
        WHERE t.DTAUSGABEDAT BETWEEN '01/10/2011' And '31/12/2011' and 
        l.sztext is not null
        order by datevente, transaction

